﻿using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using zzh_the_Visual_Studio.Models;

namespace zzh_the_Visual_Studio.DAL
{
    public class DAL_Teachers
    {
        private MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
        //查询教师姓名、员工号
        public List<teachers> getTeachers()
        {
            var list = mySqlConnection.Query<teachers>("SELECT teacher_name,t_number FROM teachers").ToList();
            return list;
        }
        /// <summary>
        /// 这是查询教师个人信息的数据
        /// </summary>
        /// <param name="a">查询教师个人信息所需要的值</param>
        /// <returns>查询教师个人信息的数据</returns>
        /// <remarks>by:zzh 2021/12/10</remarks>
        public List<teachers_colleges> getTeacher(teachers_colleges a)
        {
            var list = mySqlConnection.Query<teachers_colleges>("SELECT t_number,teacher_name,teacher_sex,t_phone,t_idcard,college_name FROM teachers_colleges where t_number=@t_number", a).ToList();
            return list;
        }
        /// <summary>
        /// 这是查询本班学生信息的数据
        /// </summary>
        /// <param name="a">查询本班学生信息所需要的值</param>
        /// <returns>查询本班学生信息的数据</returns>
        /// <remarks>by:zzh 2021/12/10</remarks>
        public List<teachers_students> getClassStudents(teachers_students a)
        {
            var list = mySqlConnection.Query<teachers_students>("SELECT s_name,s_sex,s_phone,s_idcard,s_year,s_number,teacher_name,class_name FROM teachers_students where teacher_name=@teacher_name order by class_name", a).ToList();
            return list;
        }
        /// <summary>
        /// 这是查询本班学生成绩的数据
        /// </summary>
        /// <param name="a">查询本班学生成绩所需要的值</param>
        /// <returns>查询本班学生成绩的数据</returns>
        /// <remarks>by:zzh 2021/12/10</remarks>
        public List<students_scores> getClassScores(students_scores a)
        {
            var list = mySqlConnection.Query<students_scores>("SELECT s_name,subject,teacher_name,s_number,score FROM students_scores where teacher_name=@teacher_name", a).ToList();
            return list;
        }
        //查询教师详细信息
        public List<teachers_colleges> detailTeachers()
        {
            var list = mySqlConnection.Query<teachers_colleges>("SELECT * FROM teachers_colleges").ToList();
            return list;
        }
        //查看学生列表
        public List<students_classes> getStudents()
        {
            var list = mySqlConnection.Query<students_classes>("SELECT * FROM students_classes").ToList();
            return list;
        }
        //模糊查询
        public List<students_classes> selectStudents(string s)
        {
            var list = mySqlConnection.Query<students_classes>($"SELECT * FROM `students_classes` WHERE CONCAT(IFNULL(`s_name`,''),IFNULL(`s_sex`,''),IFNULL(`s_phone`,''),IFNULL(`s_idcard`,''),IFNULL(`s_year`,''),IFNULL(`s_number`,''),IFNULL(`class_name`,'')) LIKE '%{s}%'").ToList();
            return list;
        }
        //修改学生信息，可修改学生姓名、性别、电话号码
        public int changeStudents(students s)
        {
            var sql = "UPDATE students SET s_name=@s_name,s_sex=@s_sex,s_phone=@s_phone WHERE s_number=@s_number";
            var affectedRows = mySqlConnection.Execute(sql, s);
            return affectedRows;
        }
        //查询成绩
        public List<students_scores> getScores()
        {
            var list = mySqlConnection.Query<students_scores>("SELECT * FROM students_scores").ToList();
            return list;
        }
    }
}